package com.vcc.core.util;

import com.vcc.core.constant.CardStatusEnum;
import com.vcc.core.enumConstant.AuthStatusEnum;
import com.vcc.core.enumConstant.AuthTypeEnum;
import com.vcc.core.enumConstant.ClearTypeEnum;
import com.vcc.core.exception.ProgramException;
import com.vcc.core.model.file.cardOrder.CardOrderFilePojo;
import com.vcc.core.model.file.cardholder.CardholderFilePojo;
import com.vcc.core.model.file.comptroller.ComptrollerFilePojo;
import com.vcc.core.model.file.rechargeLog.RechargeLogFilePojo;
import com.vcc.core.model.file.regulation.RegulationFilePojo;
import com.vcc.core.model.file.tran.AuthTranFilPojo;
import com.vcc.core.model.file.tran.ClearTranFilPojo;
import com.vcc.core.model.file.user.UserFilePojo;
import com.vcc.core.model.file.userCard.UserCardFilePojo;
import com.vcc.core.model.file.wallet.WalletFilePojo;
import com.vcc.core.model.file.walletDetail.WalletDetailFilePojo;
import com.vcc.core.model.file.withdraw.WithdrawFilePojo;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.security.GeneralSecurityException;
import java.util.List;

public class FileUtil {

    private static final Logger logger = LoggerFactory.getLogger(FileUtil.class);

    private static final String FILE_NAME_XLS = "xls";
    private static final String FILE_NAME_XLSX = "xlsx";
    /**
     * 交易信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] clearColumnNameList =
            new String[]{"交易id", "账号", "卡号", "卡ID", "商户号", "交易时间", "结算时间", "账单金额", "账单币种", "交易金额", "交易币种", "结算类型", "描述"};
    /**
     * 用户信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] userColumnNameList =
            new String[]{"账号", "邮箱", "经销商", "注册时间", "开卡数", "余额", "充值总额度", "充值点数"};
    /**
     * 交易信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] tranColumnNameList =
            new String[]{"交易Id", "源交易Id", "账号", "卡号", "卡ID", "商户号", "交易时间", "结算时间", "交易金额", "交易币种", "记账金额", "记账币种", "结算金额", "结算币种", "交易类型", "授权状态", "描述"};
    /**
     * 用户卡信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] columnNameList =
            new String[]{"卡Id", "卡号", "所属用户", "开卡时间", "有效期", "安全码", "余额", "备注", "状态", "持卡人姓", "持卡人名"};
    /**
     * 交易信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] walletDetailColumnNameList =
            new String[]{"订单号", "账号", "卡号", "事件", "操作时间", "资金方向", "金额", "账户余额", "钱包", "备注"};
    /**
     * 审计信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] comptrollerColumnNameList =
            new String[]{"操作时间", "账号", "操作账户", "事件", "状态", "操作地Ip"};
    /**
     * 充值记录信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] rechargeLogColumnNameList =
            new String[]{"订单号", "账号", "付款地址", "充值网络", "凭证（交易Hash）", "充值地址", "付款金额", "付款币种", "到账金额", "到账钱包", "备注", "状态", "申请时间", "审核时间"};

    /**
     * 提现记录信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] withdrawColumnNameList =
            new String[]{"订单号", "账号", "提现地址", "提现网络", "凭证（交易Hash）", "提现金额", "提现币种", "提现手续费", "提现钱包", "备注", "状态", "申请时间", "审核时间"};


    /**
     * 提现记录信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] cardOrderColumnNameList =
            new String[]{"订单号", "账号", "卡号", "金额", "币种", "类型", "状态", "创建时间", "完成时间", "备注"};


    /**
     * 充值记录信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] regulationColumnNameList =
            new String[]{"类型", "调账对象", "调账事件", "调账金额", "调账时间", "操作用户", "备注"};
    /**
     * 充值记录信息导出类
     *
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    private static final String[] cardholderColumnNameList =
            new String[]{"客户id", "所属用户", "姓", "名", "国家", "省份", "城市", "区域", "详细地址", "邮编", "联系电话", "邮箱", "备注"};


    public static Boolean hasNextPage(Long page, Long size, Long total) {
        if (page == null || size == null || total == null) {
            return false;
        }
        return page < ((total + size - 1) / size);
    }


    public static Workbook checkFileNameAndToWorkbook(String fileName, InputStream inputStream) {
        if (!RegexUtil.checkFileNameXls(fileName) && !RegexUtil.checkFileNameXlsx(fileName)) {
            throw new ProgramException("导入文件格式错误");
        }
        try {
            if (fileName.endsWith(FILE_NAME_XLS)) {
                return new HSSFWorkbook(inputStream);
            }
            if (fileName.endsWith(FILE_NAME_XLSX)) {
                return new XSSFWorkbook(inputStream);
            }
            throw new ProgramException("系统错误");
        } catch (IOException e) {
            logger.error("转换错误", e);
            throw new ProgramException("系统错误");
        }
    }


    public static void downXlsxFile(HttpServletResponse response, SXSSFWorkbook sxssfWorkbook, String fileName, String payPassword) {
        try {
            OutputStream outputStream = response.getOutputStream();
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
            Encryptor enc = info.getEncryptor();
            enc.confirmPassword(payPassword);
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem();
            OutputStream encryptOutPutStream = enc.getDataStream(poifsFileSystem);
            sxssfWorkbook.write(encryptOutPutStream);
            encryptOutPutStream.close();
            poifsFileSystem.writeFilesystem(outputStream);
            response.flushBuffer();
            sxssfWorkbook.close();
        } catch (GeneralSecurityException | IOException var9) {
            response.setStatus(500);
        }

    }

    public static SXSSFWorkbook createWorkBook(Integer count) {
        return new SXSSFWorkbook(count);
    }

    public static Sheet createSheet(SXSSFWorkbook sxssfWorkbook, String sheetName) {
        //获取该工作区的第一个sheet
        Sheet sheet = sxssfWorkbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(18);
        return sheet;
    }

    public static SXSSFWorkbook createTitle(String[] titles, Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createUserCardTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < columnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(columnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createUserTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < userColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(userColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createTranTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < tranColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(tranColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createClearTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < clearColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(clearColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createWalletDetailTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < walletDetailColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(walletDetailColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createComptrollerTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < comptrollerColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(comptrollerColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createCardholderTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < cardholderColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(cardholderColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createRechargeLogTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < rechargeLogColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(rechargeLogColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createWithdrawTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < withdrawColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(withdrawColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createCardOrderTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < cardOrderColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(cardOrderColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static SXSSFWorkbook createRegulationTitle(Integer count) {
        SXSSFWorkbook sxssfWorkbook = createWorkBook(count);
        Sheet sheet = createSheet(sxssfWorkbook, "sheet1");
        //创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < regulationColumnNameList.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(regulationColumnNameList[i]);
        }
        return sxssfWorkbook;
    }

    public static void setValue(Row row, Integer index, String value) {
        Cell cell = row.createCell(index);
        cell.setCellValue(value);
    }

    public static void setValue(Row row, Integer index, String value, CellStyle cellStyle) {
        Cell cell = row.createCell(index);
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    public static void downXlsxFile(HttpServletResponse response, SXSSFWorkbook sxssfWorkbook, String fileName) {
        OutputStream outputStream;
        try {
            outputStream = response.getOutputStream();
            // 配置文件下载
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            // 下载文件能正常显示中文
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            sxssfWorkbook.write(outputStream);
            response.flushBuffer();
            sxssfWorkbook.close();
        } catch (IOException e) {
            response.setStatus(500);
        }
    }

    public static void deriveUserData(
            SXSSFWorkbook sxssfWorkbook,
            List<UserFilePojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                UserFilePojo userFilePojo = dataList.get(i);
                StringBuilder usableQuota = new StringBuilder();
                StringBuilder rechargeQuota = new StringBuilder();
                StringBuilder exchangeRate = new StringBuilder();
                for (int j = 0; j < userFilePojo.getWallets().size(); j++) {
                    WalletFilePojo walletFilePojo = userFilePojo.getWallets().get(j);
                    usableQuota.append(walletFilePojo.getUsableQuota()).append(walletFilePojo.getCurrencyCode()).append("\n");
                    rechargeQuota.append(walletFilePojo.getRechargeQuota()).append(walletFilePojo.getCurrencyCode()).append("\n");
                    exchangeRate.append(walletFilePojo.getCurrencyCode()).append("钱包:").append(walletFilePojo.getExchangeRate()).append("\n");
                }
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, userFilePojo.getAccount());
                setValue(dataRow, 1, userFilePojo.getEmail());
                setValue(dataRow, 2, userFilePojo.getParentAccount());
                setValue(dataRow, 3, userFilePojo.getRegisterDate().toString());
                setValue(dataRow, 4, userFilePojo.getOpenCardCount().toString());
                setValue(dataRow, 5, usableQuota.toString().substring(0, usableQuota.toString().length() - 1), cellStyle);
                setValue(dataRow, 6, rechargeQuota.toString().substring(0, rechargeQuota.toString().length() - 1), cellStyle);
                setValue(dataRow, 7, exchangeRate.toString().substring(0, exchangeRate.toString().length() - 1), cellStyle);
            }
        }
    }

    public static void deriveTranData(
            SXSSFWorkbook sxssfWorkbook,
            List<AuthTranFilPojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                AuthTranFilPojo authTranFilPojo = dataList.get(i);
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, authTranFilPojo.getTranId());
                setValue(dataRow, 1, authTranFilPojo.getOriginalTranId());
                setValue(dataRow, 2, authTranFilPojo.getAccount());
                setValue(dataRow, 3, StringUtil.hideCardNumber(authTranFilPojo.getCardNumber()));
                setValue(dataRow, 4, authTranFilPojo.getCardId().toString());
                setValue(dataRow, 5, authTranFilPojo.getMerchantName());
                setValue(dataRow, 6, authTranFilPojo.getTranDate());
                setValue(dataRow, 7, authTranFilPojo.getSettleDate());
                setValue(dataRow, 8, authTranFilPojo.getMerchantAmount().toString());
                setValue(dataRow, 9, authTranFilPojo.getMerchantCurrency());
                setValue(dataRow, 10, authTranFilPojo.getBillingAmount().toString());
                setValue(dataRow, 11, authTranFilPojo.getBillingCurrency());
                setValue(dataRow, 12, authTranFilPojo.getSettleAmount() == null ? "" : authTranFilPojo.getSettleAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 13, authTranFilPojo.getSettleCurrency() == null ? "" : authTranFilPojo.getSettleCurrency());
                setValue(dataRow, 14, AuthTypeEnum.getByStatus(authTranFilPojo.getAuthType()).getTitle());
                setValue(dataRow, 15, AuthStatusEnum.getByStatus(authTranFilPojo.getStatus()).getTitle());
                setValue(dataRow, 16, authTranFilPojo.getRemark());
            }
        }
    }

    public static void deriveCardData(
            SXSSFWorkbook sxssfWorkbook,
            List<UserCardFilePojo> dataList,
            Long page,
            Long size,
            Boolean hideFlag,
            Boolean roStatus
    ) {
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                UserCardFilePojo userCardFilePojo = dataList.get(i);
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, userCardFilePojo.getId().toString());
                setValue(dataRow, 1, hideFlag ? StringUtil.hideCardNumber(userCardFilePojo.getCardNumber()) : roStatus ? StringUtil.hideCardNumber(userCardFilePojo.getCardNumber()) : userCardFilePojo.getCardNumber());
                setValue(dataRow, 2, userCardFilePojo.getAccount());
                setValue(dataRow, 3, userCardFilePojo.getRequestDate());
                setValue(dataRow, 4, hideFlag ? "" : roStatus ? "***" : userCardFilePojo.getExpiry());
                setValue(dataRow, 5, hideFlag ? "" : roStatus ? "***" : userCardFilePojo.getCvv());
                setValue(dataRow, 6, userCardFilePojo.getUsableQuota().toString());
                setValue(dataRow, 7, userCardFilePojo.getRemark());
                setValue(dataRow, 8, CardStatusEnum.fromCode(userCardFilePojo.getCardStatus()).getDesc());
                setValue(dataRow, 9, userCardFilePojo.getFirstName());
                setValue(dataRow, 10, userCardFilePojo.getLastName());
            }
        }
    }

    public static void deriveClearData(
            SXSSFWorkbook sxssfWorkbook,
            List<ClearTranFilPojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                ClearTranFilPojo clearTranFilPojo = dataList.get(i);
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, clearTranFilPojo.getTranId());
                setValue(dataRow, 1, clearTranFilPojo.getAccount());
                setValue(dataRow, 2, StringUtil.hideCardNumber(clearTranFilPojo.getCardNumber()));
                setValue(dataRow, 3, clearTranFilPojo.getCardId().toString());
                setValue(dataRow, 4, clearTranFilPojo.getMerchantName());
                setValue(dataRow, 5, clearTranFilPojo.getTranDate());
                setValue(dataRow, 6, clearTranFilPojo.getPostingDate());
                setValue(dataRow, 7, RegexUtil.checkObjectIsNull(clearTranFilPojo.getMerchantAmount()) ? "" : clearTranFilPojo.getMerchantAmount().toString());
                setValue(dataRow, 8, RegexUtil.checkStringIsNull(clearTranFilPojo.getMerchantCurrency()) ? "" : clearTranFilPojo.getMerchantCurrency());
                setValue(dataRow, 9, clearTranFilPojo.getBillingAmount().toString());
                setValue(dataRow, 10, clearTranFilPojo.getBillingCurrency());
                setValue(dataRow, 11, ClearTypeEnum.getByStatus(clearTranFilPojo.getType()).getTitle());
                setValue(dataRow, 12, clearTranFilPojo.getRemark());
            }
        }
    }

    public static void deriveWalletDetailData(
            SXSSFWorkbook sxssfWorkbook,
            List<WalletDetailFilePojo> dataList,
            Long page,
            Long size,
            Boolean roStatus
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                WalletDetailFilePojo walletDetailFilePojo = dataList.get(i);
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, walletDetailFilePojo.getOrderId());
                setValue(dataRow, 1, roStatus ? StringUtil.hideMobile(walletDetailFilePojo.getAccount()) : walletDetailFilePojo.getAccount());
                setValue(dataRow, 2, StringUtils.isBlank(walletDetailFilePojo.getCardNumber()) ? "" : StringUtil.hideCardNumber(walletDetailFilePojo.getCardNumber()));
                setValue(dataRow, 3, useFlagToString(walletDetailFilePojo.getUseFlag()));
                setValue(dataRow, 4, walletDetailFilePojo.getRequestDate());
                setValue(dataRow, 5, walletDetailFilePojo.getHasTransferIn() ? "入账" : "出账");
                setValue(dataRow, 6, walletDetailFilePojo.getAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 7, walletDetailFilePojo.getWalletAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 8, walletDetailFilePojo.getCurrencyName() + "钱包");
                setValue(dataRow, 9, walletDetailFilePojo.getRemark());
            }
        }
    }

    public static void deriveComptrollerData(
            SXSSFWorkbook sxssfWorkbook,
            List<ComptrollerFilePojo> dataList,
            Long page,
            Long size,
            Boolean roStatus
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                ComptrollerFilePojo comptrollerFilePojo = dataList.get(i);
                //((page - 1) * size) + (i + 1))
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, comptrollerFilePojo.getRequestDate());
                setValue(dataRow, 1, roStatus ? StringUtil.hideMobile(comptrollerFilePojo.getAccount()) : comptrollerFilePojo.getAccount());
                setValue(dataRow, 2, roStatus ? StringUtil.hideMobile(comptrollerFilePojo.getOperationUserAccount()) : comptrollerFilePojo.getOperationUserAccount());
                setValue(dataRow, 3, comptrollerUseFlagToString(comptrollerFilePojo.getUseFlag()));
                setValue(dataRow, 4, statusToString(comptrollerFilePojo.getStatus()));
                setValue(dataRow, 5, comptrollerFilePojo.getRequestIp());

            }
        }
    }

    public static void deriveRechargeLogData(
            SXSSFWorkbook sxssfWorkbook,
            List<RechargeLogFilePojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                RechargeLogFilePojo rechargeLogFilePojo = dataList.get(i);
                //((page - 1) * size) + (i + 1))
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, rechargeLogFilePojo.getOrderId());
                setValue(dataRow, 1, rechargeLogFilePojo.getAccount());
                setValue(dataRow, 2, rechargeLogFilePojo.getRemittanceAddress());
                setValue(dataRow, 3, rechargeLogFilePojo.getNetwork());
                setValue(dataRow, 4, rechargeLogFilePojo.getHash());
                setValue(dataRow, 5, rechargeLogFilePojo.getRechargeAddress());
                setValue(dataRow, 6, rechargeLogFilePojo.getRemittanceAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 7, rechargeLogFilePojo.getRemittanceCurrency());
                setValue(dataRow, 8, rechargeLogFilePojo.getAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 9, rechargeLogFilePojo.getCurrencyName() + "钱包");
                setValue(dataRow, 10, rechargeLogFilePojo.getRemark());
                setValue(dataRow, 11, statusToString(rechargeLogFilePojo.getStatus()));
                setValue(dataRow, 12, rechargeLogFilePojo.getRequestDate());
                setValue(dataRow, 13, rechargeLogFilePojo.getAuditDate());
            }
        }
    }


    public static void deriveWithdrawData(
            SXSSFWorkbook sxssfWorkbook,
            List<WithdrawFilePojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                WithdrawFilePojo withdrawFilePojo = dataList.get(i);
                //((page - 1) * size) + (i + 1))
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, withdrawFilePojo.getOrderId());
                setValue(dataRow, 1, withdrawFilePojo.getAccount());
                setValue(dataRow, 2, withdrawFilePojo.getAddress());
                setValue(dataRow, 3, withdrawFilePojo.getNetwork());
                setValue(dataRow, 4, withdrawFilePojo.getHash());
                setValue(dataRow, 5, withdrawFilePojo.getAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 6, withdrawFilePojo.getCurrency());
                setValue(dataRow, 7, withdrawFilePojo.getFee().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 8, withdrawFilePojo.getCurrencyName() + "钱包");
                setValue(dataRow, 9, withdrawFilePojo.getRemark());
                setValue(dataRow, 10, statusToString(withdrawFilePojo.getStatus()));
                setValue(dataRow, 11, withdrawFilePojo.getRequestDate());
                setValue(dataRow, 12, withdrawFilePojo.getAuditDate());


            }
        }
    }

    public static void deriveCardOrderData(
            SXSSFWorkbook sxssfWorkbook,
            List<CardOrderFilePojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                CardOrderFilePojo cardOrderFilePojo = dataList.get(i);
                //((page - 1) * size) + (i + 1))
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, cardOrderFilePojo.getOrderNo());
                setValue(dataRow, 1, cardOrderFilePojo.getAccount());
                setValue(dataRow, 2, cardOrderFilePojo.getCardNumber());
                setValue(dataRow, 3, cardOrderFilePojo.getOrderAmount().setScale(2, RoundingMode.HALF_UP).toString());
                setValue(dataRow, 4, cardOrderFilePojo.getOrderCurrency());
                setValue(dataRow, 5, cardOrderTypeToString(cardOrderFilePojo.getOrderType()));
                setValue(dataRow, 6, cardOrderStatusToString(cardOrderFilePojo.getOrderStatus()));
                setValue(dataRow, 7, cardOrderFilePojo.getRequestTime());
                setValue(dataRow, 8, cardOrderFilePojo.getCompleteTime());
                setValue(dataRow, 9, cardOrderFilePojo.getOrderRemark());
            }
        }
    }

    public static void deriveRegulationData(
            SXSSFWorkbook sxssfWorkbook,
            List<RegulationFilePojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                RegulationFilePojo regulationFilePojo = dataList.get(i);
                //((page - 1) * size) + (i + 1))
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, "account".equals(regulationFilePojo.getType()) ? "账户调账" : "卡调账");
                setValue(dataRow, 1, regulationFilePojo.getAccount());
                setValue(dataRow, 2, regulationUseFlagToString(regulationFilePojo.getUseFlag()));
                setValue(dataRow, 3, regulationFilePojo.getAmount().toString());
                setValue(dataRow, 4, regulationFilePojo.getRequestDate());
                setValue(dataRow, 5, regulationFilePojo.getOpAccount());
                setValue(dataRow, 5, regulationFilePojo.getRemark());
            }
        }
    }

    public static void deriveCardholderData(
            SXSSFWorkbook sxssfWorkbook,
            List<CardholderFilePojo> dataList,
            Long page,
            Long size
    ) {
        CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 换行的关键，自定义单元格内容换行规则
        cellStyle.setWrapText(true);
        SXSSFSheet sheet = sxssfWorkbook.getSheet("sheet1");
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                CardholderFilePojo cardholderFilePojo = dataList.get(i);
                //((page - 1) * size) + (i + 1))
                Row dataRow = sheet.createRow(Math.toIntExact(((page - 1) * size) + (i + 1)));
                setValue(dataRow, 0, cardholderFilePojo.getId().toString());
                setValue(dataRow, 1, StringUtil.hideMobile(cardholderFilePojo.getAccount()));
                setValue(dataRow, 2, cardholderFilePojo.getFirstName());
                setValue(dataRow, 3, cardholderFilePojo.getLastName());
                setValue(dataRow, 4, cardholderFilePojo.getCountry());
                setValue(dataRow, 5, cardholderFilePojo.getProvince());
                setValue(dataRow, 6, cardholderFilePojo.getCity());
                setValue(dataRow, 7, cardholderFilePojo.getArea());
                setValue(dataRow, 8, cardholderFilePojo.getAddress());
                setValue(dataRow, 9, cardholderFilePojo.getPostCode());
                setValue(dataRow, 10, cardholderFilePojo.getPhone());
                setValue(dataRow, 11, cardholderFilePojo.getEmail());
                setValue(dataRow, 12, cardholderFilePojo.getRemark());
            }
        }
    }

    private static String regulationUseFlagToString(Integer useFlag) {
        switch (useFlag) {
            case 0: {
                return "活动返现";
            }
            case 1: {
                return "拒付回退";
            }
            case 2: {
                return "罚金";
            }
            default: {
                return "超时未清算";
            }
        }
    }

    /**
     * 0 注册 1 登录  2  禁用  3 启用   4 注销  5  修改密码  6 修改支付密码
     */
    private static String comptrollerUseFlagToString(Integer useFlag) {
        switch (useFlag) {
            case 0: {
                return "注册";
            }
            case 1: {
                return "登录";
            }
            case 2: {
                return "禁用";
            }
            case 3: {
                return "启用";
            }
            case 4: {
                return "注销";
            }
            case 5: {
                return "修改密码";
            }
            case 6: {
                return "修改支付密码";
            }
            default: {
                return "未知";
            }
        }
    }

    private static String useFlagToString(Integer useFlag) {
        return switch (useFlag) {
            case 0 -> "开卡手续费";
            case 1 -> "卡充值";
            case 2 -> "卡转出";
            case 3 -> "账户充值";
            case 4 -> "账户转出";
            case 5 -> "销卡转入";
            case 6 -> "卡充值手续费";
            case 7 -> "登录";
            case 8 -> "禁用";
            case 9 -> "启用";
            case 10 -> "注销";
            case 11 -> "修改密码";
            case 12 -> "冻结";
            case 13 -> "解冻";
            case 14 -> "授权撤销";
            default -> "退款";
        };
    }

    private static String statusToString(Integer status) {
        switch (status) {
            case 0: {
                return "成功";
            }
            case 1: {
                return "失败";
            }
            case 2: {
                return "等待中";
            }
            default: {
                return "未知";
            }
        }
    }

    private static String cardOrderStatusToString(String status) {
        //SUCCESS
        //ERROR
        //PENDING
        switch (status) {
            case "SUCCESS": {
                return "成功";
            }
            case "ERROR": {
                return "失败";
            }
            case "PENDING": {
                return "等待中";
            }
            default: {
                return "未知";
            }
        }
    }

    private static String cardOrderTypeToString(String type) {
        //OPEN
        //RECHARGE
        //WITHDRAW
        //BLOCK
        //UNBLOCK
        //CLOSE
        switch (type) {
            case "OPEN": {
                return "开卡";
            }
            case "RECHARGE": {
                return "充值";
            }
            case "WITHDRAW": {
                return "提现";
            }
            case "BLOCK": {
                return "冻结";
            }
            case "UNBLOCK": {
                return "解冻";
            }
            case "CLOSE": {
                return "销卡";
            }
            default: {
                return "未知";
            }
        }
    }
}
